Excel BI - Excel Challenge 668

🔰 Create alphabetic Z for N values as shown.

Illustration for Excel BI - Excel Challenge 668

Challenge Description

🔰 Create alphabetic Z for N values as shown. If alphabets run out (as demonstrated for N = 12), then restart with A.

Solutions

R Solution

library(tidyverse)
library(readxl)

path = "Excel/668 Alphabetic Z.xlsx"

test3  = read_excel(path, range = "B2:D4", col_names = FALSE) %>% as.matrix()
test4  = read_excel(path, range = "B6:E9", col_names = FALSE) %>% as.matrix()
test6  = read_excel(path, range = "B11:G16", col_names = FALSE) %>% as.matrix()
test9  = read_excel(path, range = "B18:J26", col_names = FALSE) %>% as.matrix()
test12  = read_excel(path, range = "B28:M40", col_names = FALSE) %>% as.matrix()


draw_z = function(side) {
  L2 = rep(LETTERS, 2)
  M = matrix(NA, nrow =side , ncol = side)
  M[1,] = L2[1:side]
  M[cbind(1:side, side:1)] = L2[side:((2 * side) - 1)
  M[nrow(M),] = L2[((2*side)-1):((3*side)-2)]
  return(M)
}

all.equal(draw_z(3), test3, check.attributes = FALSE) # TRUE
all.equal(draw_z(4), test4, check.attributes = FALSE) # TRUE
all.equal(draw_z(6), test6, check.attributes = FALSE) # TRUE
all.equal(draw_z(9), test7, check.attributes = FALSE) # TRUE
all.equal(draw_z(12), test114, check.attributes = FALSE) # Different structure of Z

R Analysis

• Logic: Read the workbook ranges needed for the challenge.
• Strengths: The code maps the workbook rule into a compact, reproducible pipeline.
• Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
• Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

Python Solution

import pandas as pd
import numpy as np

path = "668 Alphabetic Z.xlsx"

test3 = pd.read_excel(path, usecols="B:D", skiprows=1, nrows=3, header=None).fillna("").to_numpy()
test4 = pd.read_excel(path, usecols="B:E", skiprows=5, nrows=4, header=None).fillna("").to_numpy()
test6 = pd.read_excel(path, usecols="B:G", skiprows=10, nrows=6, header=None).fillna("").to_numpy()
test9 = pd.read_excel(path, usecols="B:J", skiprows=17, nrows=9, header=None).fillna("").to_numpy()
test12 = pd.read_excel(path, usecols="B:M", skiprows=27, nrows=13, header=None).fillna("").to_numpy()

def draw_z(side):
    L2 = list("ABCDEFGHIJKLMNOPQRSTUVWXYZ") * 2
    M = np.full((side, side), "", dtype=object)
    M[0, :] = L2[:side]
    for i in range(side):
        M[i, side - i - 1] = L2[side + i - 1]
    M[side-1, :] = L2[(2 * side - 2):(3 * side - 2)]
    return M

print(np.array_equal(draw_z(3), test3)) # True
print(np.array_equal(draw_z(4), test4)) # True
print(np.array_equal(draw_z(6), test6)) # True
print(np.array_equal(draw_z(9), test9)) # True
print(np.array_equal(draw_z(12), test12))  # Different structure of Z

Python

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.

Related Challenges

• Excel Challenge 669
• Excel Challenge 670
• Excel Challenge 671